package vip.xiaonuo.bi.core.datasource.util;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

public class ExcelCommonUtil {

    /**
     * 判断是否是2003的excel，返回true是2003
     * 
     * @param fileName
     * @return
     */
    public static boolean isExcel2003(String fileName) {
        return fileName.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 判断是否是2007的excel，返回true是2007
     * 
     * @param fileName
     * @return
     */
    public static boolean isExcel2007(String fileName) {
        return fileName.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 判断文件是否是Excel文件
     * 
     * @param fileName
     * @return
     */
    public static boolean validateExcel(String fileName) {
        if (fileName == null || !(isExcel2003(fileName) || isExcel2007(fileName))) {
            return false;
        }
        return true;
    }

    /**
     * 得到workbook
     * 
     * @param fileName
     * @param is
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(String fileName, InputStream is) throws Exception {
        Workbook workbook = null;
        try {
            /** 判断文件的类型，是2003还是2007 */
            boolean isExcel2003 = true;
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }

            if (isExcel2003) {
                workbook = new HSSFWorkbook(is);
            } else {
                workbook = new XSSFWorkbook(is);
            }
        } catch (Exception e) {
            throw e;
        }
        return workbook;
    }

    /**
     * 得到Cell的值
     * 
     * @param cell
     * @return
     */
    public static String getValue(Cell cell) throws Exception {
        if (cell != null) {
            if (cell.getCellType() == CellType.BOOLEAN) {
                return String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellType() == CellType.NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                    return new SimpleDateFormat("yyyy-MM-dd").format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    return new BigDecimal(value).toString();
                }
            } else if (cell.getCellType() == CellType.STRING) {
                String value = String.valueOf(cell.getStringCellValue());
                Pattern pattern = Pattern.compile("[0-9]{4}.[0-9]{1,2}.[0-9]{1,2}");
                if (pattern.matcher(value).matches()) {
                    return value.replace(".", "-");
                } else {
                    return value;
                }
            } else {
                return String.valueOf(cell.getStringCellValue());
            }
        }
        return "";
    }

    /**
     * 判断一个sheet是否有数据
     * 
     * @param sheet
     * @return 空的sheet返回true
     */
    public static boolean isEmptySheet(Sheet sheet) {
        boolean isEmpty = false;
        if ((sheet.getLastRowNum() == 0
            && (sheet.getPhysicalNumberOfRows() == 0 || sheet.getPhysicalNumberOfRows() == 1))) {
            isEmpty = true;
        }
        return isEmpty;
    }

    /**
     * 判断一行是否为空
     * 
     * @param row
     * @return
     */
    public static boolean isRowEmpty(Row row) {
        boolean isRowEmpty = true;
        if (row != null) {
            for (int i = row.getFirstCellNum(), len = row.getLastCellNum(); i <= len; i++) {
                Cell cell = row.getCell(i);
                if (cell != null && cell.getCellType() != CellType.BLANK && !"".equals((cell + "").trim())) {
                    isRowEmpty = false;
                }
            }
        }
        return isRowEmpty;
    }

    /**
     * 解析文件的sheet数据
     * 
     * @param fileName 文件名
     * @param is 流
     * @param startNum 开始行数（标题行）
     * @param sheetNameList 工作表空间名称为空时获取所有内容，不过空时获取指定内容
     * @return
     * @throws Exception
     */
    public static Map<String, Object> getExcelRead(String fileName, InputStream is, int startNum, List<String> sheetNameList) throws Exception {
        Map<String, Object> map = new HashMap<>();
        // 判断其兼容版本 调用了判断版本的方法
        Workbook workbook = getWorkbook(fileName, is);
        int sheetNum = workbook.getNumberOfSheets();// 得到sheet的个数
        List<Row> rowList = null;
        List<String> titleList = null;
        Map<String, Object> rowMap = null;
        boolean isEmptySheetNameList = (sheetNameList == null || sheetNameList.size() == 0);
        for (int n = 0; n < sheetNum; n++) {
            rowList = new ArrayList<Row>();
            titleList = new ArrayList<>();
            rowMap = new HashMap<>();
            Sheet sheet = workbook.getSheetAt(n);
            String sheetName = sheet.getSheetName();
            if ((isEmptySheetNameList || sheetNameList.contains(sheetName)) && !isEmptySheet(sheet)) {// sheetNames存在时要满足在此list集合中取值
                Row row = null;
                for (int i = sheet.getFirstRowNum(), len = sheet.getLastRowNum(); i <= len; i++) {
                    row = sheet.getRow(i);
                    if (i == startNum - 1) {
                        titleList = getRowValue(row);
                    }
                    if (i >= startNum) {
                        if (!isRowEmpty(row)) {
                            rowList.add(row);
                        }
                    }
                }
                rowMap.put("titleList", titleList);
                rowMap.put("rowList", rowList);
                map.put(sheetName, rowMap);
            }
        }
        is.close();
        return map;
    }

    /**
     * 获取一行的数据
     * 
     * @param row
     * @return
     */
    private static List<String> getRowValue(Row row) throws Exception {
        List<String> list = null;
        if (!isRowEmpty(row)) {
            list = new ArrayList<>();
            for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
                Cell cell = row.getCell(i);
                if (cell != null && cell.getCellType() != CellType.BLANK) {
                    String cellValue = ExcelCommonUtil.getValue(cell);
                    list.add(cellValue);
                }
            }
        }
        return list;
    }
}